package userInteraction;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

@SuppressWarnings("serial")
public class createNewUser implements Serializable {

	public Boolean checkEmail(String email) throws Exception{
		Connection con = null;
		try{
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection("jdbc:mysql://localhost:8889/licenseDb","root", "root");

			PreparedStatement query = (PreparedStatement) con.prepareStatement("select * from Logins where emailAddress=?");
			query.setString(1, email);
			ResultSet rs = query.executeQuery();

			rs.last();			
			Integer rowCount = rs.getRow();
			if (rowCount==0){
				//Email address does not exist in DB
				return true;
			}
		}
		catch (Exception e){
			System.out.println(e.getMessage());
		}
		finally{
			try {
				if(con != null)
					con.close();
			} 
			catch(Exception e) {}
		}
		//Email address exists in DB
		return false;
	}

	public Boolean createUser(String emailAddress, String firstName, String lastName, String password,String phoneNumber,String city) throws Exception{
		Connection con = null;
		try{
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection("jdbc:mysql://localhost:8889/licenseDb","root", "root");

			if (checkEmail(emailAddress)==true){
				PreparedStatement insertCustomer =(PreparedStatement) con.prepareStatement("insert into Customer(firstName, lastName, phoneNumber, city, emailAddress) values(?, ?, ?, ?, ?)");
				insertCustomer.setString(1, firstName);
				insertCustomer.setString(2, lastName);
				insertCustomer.setString(3, phoneNumber);
				insertCustomer.setString(4, city);
				insertCustomer.setString(5, emailAddress);
				Integer result = insertCustomer.executeUpdate();

				if(result==1){
					PreparedStatement selectCustomerId =(PreparedStatement) con.prepareStatement("select customerId from Customer where emailAddress=?");
					selectCustomerId.setString(1, emailAddress);

					ResultSet results = selectCustomerId.executeQuery();
					Integer customerId = null;
					while(results.next()){
						customerId = results.getInt("customerId");
					}

					PreparedStatement insertLogins=(PreparedStatement)  con.prepareStatement("insert into Logins(customerId, emailAddress, password, roleId) values(?, ?, ?, 1)");
					insertLogins.setInt(1, customerId);
					insertLogins.setString(2, emailAddress);
					insertLogins.setString(3, password);
					Integer rowCountFinal = insertLogins.executeUpdate();
					if(rowCountFinal == 1){
						return true;
					}
				}	
			}
		}
		catch (Exception e){
			System.out.println(e.getMessage());
		}
		finally{
			try {
				if(con != null)
					con.close();
			} 
			catch(Exception e) {}	
		}
		return false;
	}	

}
